package com.dob.dbo.jdbc;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;

import com.dob.model.Employee;
public class CRUDUsingJDBCTemplate {
	
		@Autowired
		private DataSource dataSource;
		private JdbcTemplate jdbcTemplate;

		public DataSource getDataSource() {
			return dataSource;
		}

		public void setDataSource(DataSource dataSource) {
			this.dataSource = dataSource;
			this.jdbcTemplate= new JdbcTemplate(dataSource);
		}
		
		public void insertEmployee(Employee emp){
			String sql="INSERT INTO EMPLOYEE(EMPNAME,EMPID,SALARY) VALUES(?,?,?)";
			jdbcTemplate.update(sql, new Object[]{emp.getEmpName()+"_INS",emp.getEmpId()+1,emp.getSalary()+200});
		}
		
		public Employee getEmployee(int empId){
			String sql="SELECT * FROm EMPLOYEE WHERE EMPID=?";
			return jdbcTemplate.queryForObject(sql, new Object[]{empId}, new EmployeeMappper());
		}
		
		public List<Employee> getAllEmployee(){
			String sql="SELECT * FROM EMPLOYEE";
			return jdbcTemplate.query(sql, new EmployeeMappper());
		}
		
		class EmployeeMappper implements RowMapper<Employee>{

			@Override
			public Employee mapRow(ResultSet rs, int rowNo)
					throws SQLException {
				Employee emp= new Employee();
				emp.setEmpId(rs.getInt("EMPID"));
				emp.setEmpName(rs.getString("EMPNAME"));
				emp.setSalary(rs.getInt("SALARY"));
				return emp;
			}
			
			
		}
				
				
}
